const sqlEX = require('../../database/vod/query')
const xlsx = require('node-xlsx')
const fs = require('fs')
const path = require("path")
const dayjs = require("dayjs")

// 获取课程分类
const getSubjectList = async (req, res) => {
  const id = req.params.id
  // 查询parent_id的父级分类
  const sql1 = `SELECT * FROM subject WHERE parent_id = ${id}`
  let result = await sqlEX.sqlExcute(sql1)
  // 获取result里面的所有的id
  let idArr = []
  result.forEach(item => {
    idArr.push(item.id)
  })
  // 查询idArr里面的所有的子级分类
  for (const item of idArr) {
    const index = idArr.indexOf(item)
    const sql2 = `SELECT * FROM subject WHERE parent_id = ${item}`
    let res = await sqlEX.sqlExcute(sql2)
    if (res.length > 0) {
      result[index].children = res
    }
  }

  res.send({
    status: 1,
    code: 20000,
    data: result

  })

  //res.send({
  //  status: 1,
  //  code: 20000,
  //  data: {
  //
  //  }
  //})
}

// 课程导出为excel
const exportSubject = async (req, res) => {
  const sql = `SELECT * FROM subject`
  let result = await sqlEX.sqlExcute(sql)

  // 将数据写入excel
  const data = [
    ['id', '分类名称', '上级id', '排序'],
    ...result.map(item => [item.id, item.title, item.parent_id, item.sort])
  ]
  // 将数据写入excel
  const buffer = xlsx.build([{ name: '课程分类', data }])
  // 设置响应头
  res.setHeader('Content-Type', 'application/vnd.openxmlformats')
  res.setHeader('Content-Disposition', 'attachment; filename=subject.xlsx')
  res.send(buffer)
}

// 课程导入
const importSubject = async (req, res) => {
  // 文件的正确存放路径
  let name = path.join(__dirname, '../../public/upload/excel/' + req.file.originalname)
  const oldName = req.file.path
  // 重写文件名
  fs.renameSync(oldName, name)//将老的文件名改成新的有后缀的文件 #同步写法

  // 读取excel文件 name是路径 通过路径解析文件
  const { data } = xlsx.parse(name)[0]
  const dataArr1 = data.slice(1) // 去除第一行(表头)
  // 转成数组对象的形式(数组内的每一项是一个对象)
  const dataArr2 = dataArr1.map(item => {
    return {
      title: item[1],
      parent_id: item[2],
      sort: item[3]
    }
  })
  console.log(dataArr2)
  const sql = `INSERT INTO subject set?`
  for (const item of dataArr2) {
    // 查询是否有重复的分类
    const sql1 = `SELECT * FROM subject WHERE title = '${item.title}'`
    let result = await sqlEX.sqlExcute(sql1)
    // 没有重复的分类就执行插入
    let results = result.length > 0 ? null : await sqlEX.sqlExcute(sql, item)
  }
  res.send({
    status: 1,
    code: 20000,
    msg: '导入成功'
  })
}

// 获取课程列表
const getCourseList = async (req, res) => {
  // 获取请求参数
  let page = req.params.page || 1
  let pageSize = req.params.pageSize || 10
  const searchObj = req.query || {}
  if (page === 'get') {
    page = 1
    pageSize = 10
  }

  // 对象修改键名
  let obj = {}
  if (Object.keys(searchObj).length > 0) {
    Object.keys(searchObj).map(item => {
      let key = ''
      if (item === 'subjectId') {
        key = 'subject_id'
      } else if (item === 'subjectParentId') {
        key = 'subject_parent_id'
      } else if (item === 'teacherId') {
        key = 'teacher_id'
      } else if (item === 'title') {
        key = 'title'
      }
      if (searchObj[item]) {
        obj[key] = searchObj[item]
      }
    })
  }
  //
  let result = [], result1 = []
  // 查询所有的课程分页
  if (Object.keys(obj).length > 0) {
    // 查询条件不为空
    const sql = `SELECT * FROM course WHERE 1=1 ${sqlEX.getSearchSql(obj)}`
    result = await sqlEX.sqlExcute(sql)
    result1 = [{
      total: result.length
    }]
  } else {
    const sql = `SELECT * FROM course LIMIT ${(page - 1) * pageSize},${pageSize}`
    result = await sqlEX.sqlExcute(sql)
    // 查询所有课程的总数
    const sql1 = `SELECT COUNT(*) AS total FROM course`
    result1 =  await sqlEX.sqlExcute(sql1)
  }

  // 为每个item增加param属性 表述父级信息
  for (const item of result) {
    // 查询其所在的二级分类
    let subjectId = item.subject_id
    let sql1 = `SELECT * FROM subject WHERE id = ${subjectId}`
    let result1 = await sqlEX.sqlExcute(sql1)
    // 查询其所在的一级分类
    let subjectParentId = item.subject_parent_id
    let sql2 = `SELECT * FROM subject WHERE id = ${subjectParentId}`
    let result2 = await sqlEX.sqlExcute(sql2)
    // 查询讲师
    let teacherId = item.teacher_id
    let sql3 = `SELECT * FROM teacher WHERE id = ${teacherId}`
    let result3 = await sqlEX.sqlExcute(sql3)
    item.param = {
      subjectTitle: result1[0].title,
      subjectParentTitle: result2[0].title,
      teacher_name: result3[0].name
    }
  }


  res.send({
    status: 1,
    code: 20000,
    data: {
      records: result,
      totalCount: result1[0].total
    }
  })
}

// 根据id获取课程信息
const getCourseById = async (req, res) => {
  const id = req.params.id
  const sql = `SELECT * FROM course WHERE id = ${id}`
  const result = await sqlEX.sqlExcute(sql)

  res.send({
    status: 1,
    code: 20000,
    data: {
      ...result[0]
    }
  })
}

// 更新
const updateCourse = async (req, res) => {
  const courseInfo = req.body
  const id = courseInfo.id
  // 删除一些字段
  delete courseInfo.publish_time
  delete courseInfo.create_time
  delete courseInfo.id
  courseInfo.update_time = dayjs(courseInfo.update_time).format('YYYY-MM-DD HH:mm:ss')


  const sql = `UPDATE course SET ? where id=${id}`
  const result = await sqlEX.sqlExcute(sql, courseInfo)
  res.send({
    status: 1,
    code: 20000,
    message: '更新成功',
    data: id
  })
}

// 获取课程章节
const getCourseChapter = async (req, res) => {
  const id = req.params.id
  const sql = `SELECT * FROM chapter WHERE course_id = ${id}`
  const result = await sqlEX.sqlExcute(sql)
  res.send({
    status: 1,
    code: 20000,
    data: result
  })
}

// 获取课程章节
const getCourseChapterById = async (req, res) => {
  const id = req.params.id
  const sql = `SELECT * FROM chapter WHERE id = ${id}`
  const result = await sqlEX.sqlExcute(sql)
  res.send({
    status: 1,
    code: 20000,
    data: result[0]
  })
}

// 更新章节
const updateChapter = async (req, res) => {
  const chapterInfo = req.body
  const id = chapterInfo.id
  // 删除一些字段
  delete chapterInfo.create_time
  delete chapterInfo.id
  chapterInfo.update_time = dayjs(chapterInfo.update_time).format('YYYY-MM-DD HH:mm:ss')

  const sql = `UPDATE chapter SET ? where id=${id}`
  const result = await sqlEX.sqlExcute(sql, chapterInfo)
  res.send({
    status: 1,
    code: 20000,
    message: '更新成功',
    data: id
  })
}

// 添加章节
const addChapter = async (req, res) => {
  const chapterInfo = req.body
  // 删除一些字段
  delete chapterInfo.create_time

  chapterInfo.update_time = dayjs(chapterInfo.update_time).format('YYYY-MM-DD HH:mm:ss')
  chapterInfo.create_time = dayjs(chapterInfo.create_time).format('YYYY-MM-DD HH:mm:ss')
  const sql = `INSERT INTO chapter SET ?`
  const result = await sqlEX.sqlExcute(sql, chapterInfo)
  res.send({
    status: 1,
    code: 20000,
    message: '添加成功',
    data: result.insertId
  })
}

// 删除章节
const deleteChapter = async (req, res) => {
  const id = req.params.id
  const sql = `DELETE FROM chapter WHERE id = ${id}`
  const result = await sqlEX.sqlExcute(sql)
  res.send({
    status: 1,
    code: 20000,
    message: '删除成功',
    data: id
  })
}

// 保存课程
const saveCourse = async (req, res) => {
  const chapterInfo = req.body
  // 删除一些字段
  delete chapterInfo.create_time
  delete chapterInfo.id
  chapterInfo.update_time = dayjs(chapterInfo.update_time).format('YYYY-MM-DD HH:mm:ss')

  const sql = `INSERT INTO course SET ?`
  const result = await sqlEX.sqlExcute(sql, chapterInfo)
  res.send({
    status: 1,
    code: 20000,
    message: '保存成功',
    data: result.insertId
  })
}

// 课程封面图片上传
const uploadCourseImg = async (req, res) => {
  console.log(req.file)
  const originalname = req.file.originalname
  let oldname = req.file.path //获取path: 'public\\upload\\0f625978d5d1a783b12e149718f8b634',
  let newname = path.join(__dirname, '../../public/upload/course/' + originalname)
  fs.renameSync(oldname, newname)//将老的文件名改成新的有后缀的文件 #同步写法

  res.send({
    status: 1,
    code: 20000,
    data: 'http://127.0.0.1:8080/uploads/course/' + originalname

  })
}

// 获取发布课程信息
const getPublishCourse = async (req, res) => {
  const sql = `SELECT * FROM course WHERE publish_status = 1`
  const result = await sqlEX.sqlExcute(sql)
  res.send({
    status: 1,
    code: 20000,
    data: result
  })
}

// 发布课程
const publishCourse = async (req, res) => {
  const id = req.params.id
  const sql = `UPDATE course SET status = 1 WHERE id = ${id}`
  const result = await sqlEX.sqlExcute(sql)
  res.send({
    status: 1,
    code: 20000,
    message: '发布成功',
  })
}

// 获取全部课程信息
const getAllCourseList = async (req, res) => {
  const sql = `SELECT * FROM course where is_deleted = 0`
  const result = await sqlEX.sqlExcute(sql)

  // 为每个item增加param属性 表述父级信息
  for (const item of result) {
    // 查询其所在的二级分类
    let subjectId = item.subject_id
    let sql1 = `SELECT * FROM subject WHERE id = ${subjectId}`
    let result1 = await sqlEX.sqlExcute(sql1)
    // 查询其所在的一级分类
    let subjectParentId = item.subject_parent_id
    let sql2 = `SELECT * FROM subject WHERE id = ${subjectParentId}`
    let result2 = await sqlEX.sqlExcute(sql2)
    // 查询讲师
    let teacherId = item.teacher_id
    let sql3 = `SELECT * FROM teacher WHERE id = ${teacherId}`
    let result3 = await sqlEX.sqlExcute(sql3)
    item.param = {
      subjectTitle: result1[0].title,
      subjectParentTitle: result2[0].title,
      teacher_name: result3[0].name
    }
  }

  res.send({
    status: 1,
    code: 20000,
    data: result
  })
}

module.exports = {
  getSubjectList,
  exportSubject,
  importSubject,
  getCourseList,
  getCourseById,
  updateCourse,
  getCourseChapter,
  getCourseChapterById,
  updateChapter,
  uploadCourseImg,
  saveCourse,
  addChapter,
  deleteChapter,
  getPublishCourse,
  getAllCourseList,
  publishCourse
}
